package com.wanxi.util;

import java.sql.*;
import java.util.List;

/**
 * JDBC的书写步骤
 * 1.加载驱动  整个项目只加载一次
 * 2.得到数据库的连接
 * 3.编译sql语句
 * 4.执行sql语句
 * 5.返回结果
 * 6.关闭连接
 */
public class JDBC {
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //    private static Connection conn = null;
    // 连接数据库的地址
    // jdbc:mysql://固定的单词
    // 121.199.55.96:连接数据库的ip地址：localhost,127.0.0.1，/
    // 3306/ 端口
    // canyin04?数据库名称
    // serverTimezone=UTC 定义时区
    // &characterEncoding=UTF-8 编码
    private static final String URL = "jdbc:mysql://localhost:3306/meiti?serverTimezone=UTC&characterEncoding=UTF-8";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";
    private static PreparedStatement preparedStatement = null;

    private static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    /**
     * 无论增加修改删除都是sql语句不一样，则直接传递一个sql语句即可
     *
     * @param
     * @return
     */
//    更新，一条sql语句
    public static int update(String sql) {
        try {
//            编译sql
            preparedStatement = getConnection().prepareStatement(sql);
//            执行sql

            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static ResultSet query(String sql) {
        try {
//            编译sql
            preparedStatement = getConnection().prepareStatement(sql);
//            执行sql

            return preparedStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static ResultSet query(String sql, List<Object> list) {
        try {
//            编译sql
            preparedStatement = getConnection().prepareStatement(sql);
//            执行sql select * from user where username=? and sex=? and phone=?
            for (int i = 0; i < list.size(); i++) {
                preparedStatement.setObject(i + 1, list.get(i));
            }
//            preparedStatement.setObject(1, model.getName());
//            preparedStatement.setObject(2, model.getAddress());
//            preparedStatement.setObject(3, model.getBirthdayStart());
//            preparedStatement.setObject(4, model.getBirthdayEnd());
            return preparedStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
//    public static int update(NewsModel newsModel) {
//    }

    public static int count(String sql) {
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return query.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
